package com.aier.school.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.aier.classes.entity.ClassStudent;
import com.aier.classes.entity.mapper.ClassStudentRowMapper;
import com.aier.common.common.Constant;
import com.aier.common.dao.ICommonDAO;
import com.aier.school.entity.SchoolIcCard;
import com.aier.school.entity.UserIcardInfo;
import com.aier.school.entity.UserIcardRecord;
import com.aier.school.entity.mapper.SchoolIcCardRowMapper;
import com.aier.school.entity.mapper.UserIcardInfoRowMapper;
import com.aier.school.entity.mapper.UserIcardRecordRowMapper;
import com.aier.school.service.ISchoolIcCardService;

@Component("schoolIcCardService")
public class SchoolIcCardServiceImpl_J implements ISchoolIcCardService{
    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }
    
    @Override
    public Map<String, Object> searchSchoolCardsByCondition(int page,
            int pagesize,int sid,String cardnum,short state,String account) {
        String sql = " FROM `school_icard` si LEFT JOIN ic_card ic on si.icid=ic.icid " +
                        "LEFT JOIN school_teacher_icard sti on si.icid=sti.icid " +
                        "LEFT JOIN school_student_icard ssi on si.icid=ssi.icid where si.sid=? ";
        if(cardnum!=null&&!"".equals(cardnum)){
            String head = cardnum.substring(0, 2);
            String body = cardnum.substring(2, Constant.num_8);
            sql += "and si.icid=(select icid from ic_card where ichead ='" + head + "' and icbody ='" + body + "'"+ ")";
        }
        if (state != -1) {
            sql += " and si.state =" + state;
        }
        sql += " order by si.icid,si.state";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "SELECT si.*,CONCAT(ic.ichead,ic.icbody) as cardnum,(select tname from school_teacher where tid=sti.tid) as name_teacher," +
                        "(select sname from class_student where csid=ssi.csid) as name_student"
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid }, new Object[] { sid },
                        new SchoolIcCardRowMapper(SchoolIcCardRowMapper.AND_CARDNUM));
    }
    
    @Override
    public Map<String, Object> searchSchoolCardsByConditionNew(int page,
            int pagesize,String sname,String cardnum,short state) {
        String sql = " FROM `school_icard` si LEFT JOIN ic_card ic on si.icid=ic.icid " +
                        "LEFT JOIN school_teacher_icard sti on si.icid=sti.icid " +
                        "LEFT JOIN school_student_icard ssi on si.icid=ssi.icid left join school s on si.sid = s.sid " +
                        "where (si.icid in (SELECT icid FROM school_teacher_icard) OR si.icid in (SELECT icid FROM school_student_icard))";
        if (sname!=null && !"".equals(sname)) {
            sql += " and s.name like '%" + sname + "%'";
            
        }
        if(cardnum!=null&&!"".equals(cardnum)){
            String head = cardnum.substring(0, 2);
            String body = cardnum.substring(2, Constant.num_8);
            sql += " and si.icid=(select icid from ic_card where ichead ='" + head + "' and icbody ='" + body + "'"+ ")";
            
        } 
        if (state != -1) {
            sql += " and si.state =" + state;
        }
        
        sql += " order by si.sid, si.icid,si.state";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "SELECT si.*,CONCAT(ic.ichead,ic.icbody) as cardnum,(select tname from school_teacher where tid=sti.tid) as name_teacher," +
                        "(select sname from class_student where csid=ssi.csid) as name_student,s.name as sname"
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] {}, new Object[] {},
                        new SchoolIcCardRowMapper(SchoolIcCardRowMapper.AND_SNAME));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolIcCard> searchSchoolCardList(Integer sid){
        String sql = "SELECT si.*,CONCAT(ic.ichead,ic.icbody) as cardnum FROM `school_icard` si LEFT JOIN " +
        		"ic_card ic on si.icid=ic.icid where si.sid=? ";
        return (List<SchoolIcCard>) commonDao.list(sql, new Object[] { sid },
                new SchoolIcCardRowMapper(SchoolIcCardRowMapper.AND_CARDNUM_NORMAL));        
    }
    
    @Override
    public Map<String, Object> searchSchoolCardsByCidABuid(int page,
            int pagesize, Long cid, Long buid){
        String sql = " FROM `school_icard` si LEFT JOIN ic_card ic on si.icid=ic.icid " +
                " LEFT JOIN school_student_icard ssi on si.icid=ssi.icid where ssi.csid in " +
                "(select csid from class_student where cid=? and uid in (?)) and si.state=10 ";
        sql += " order by si.icid,si.state";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "SELECT si.*,CONCAT(ic.ichead,ic.icbody) as cardnum,ic.icfoot," +
                        "(select sname from class_student where csid=ssi.csid) as name_student,"
                        +"(select cname  from classes  c where c.cid=? ) as cname,"
                        + "(select name  from school  s where s.sid=si.sid ) as sname"
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] {cid, cid,buid}, new Object[] {cid,buid },
                        new SchoolIcCardRowMapper(SchoolIcCardRowMapper.AND_CARDNUM_STUDENT));        
    }
    
    @Override
    public Map<String, Object> searchUserIcardInfoListByCidABuid(int page,
            int pagesize,Long buid){
        String sql = " FROM `user_icard_info` uii LEFT JOIN ic_card ic on uii.icid=ic.icid LEFT JOIN class_student ct on uii.uid=ct.uid " +
                " where uii.uid=?";
        sql += " order by uii.cdate desc";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "SELECT uii.*,CONCAT(ic.ichead,ic.icbody) as cardnum,ct.sname as name_student "
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { buid }, new Object[] { buid },
                        new UserIcardInfoRowMapper(UserIcardInfoRowMapper.AND_CARDNUM));          
    }
    
    @Override
    public Map<String, Object> searchUserIcardInfoListByCid(int page,
            int pagesize, Long cid){
        String sql = " FROM `user_icard_info` uii LEFT JOIN ic_card ic on uii.icid=ic.icid LEFT JOIN class_student ct on uii.uid=ct.uid " +
                " where uii.uid in (select uid from class_student where cid=?)";
        sql += " order by uii.cdate desc";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "SELECT uii.*,CONCAT(ic.ichead,ic.icbody) as cardnum,ct.sname as name_student "
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { cid }, new Object[] { cid },
                        new UserIcardInfoRowMapper(UserIcardInfoRowMapper.AND_CARDNUM));          
    }
    
    @Override
    public Map<String, Object> searchUserIcardInfoListByCondation(int page,
            int pagesize,Integer sid, Long cid,String dayBeginValue, String dayEndValue, String sname){
        String sql = " from school_student_icard ssi INNER JOIN user_icard_info uii on ssi.icid=uii.icid INNER JOIN ic_card ic on ssi.icid=ic.icid " +
        		"INNER JOIN class_student cs on ssi.csid=cs.csid where 1=1 ";
        if(sid!=null && sid!=0){
            sql += " and ssi.sid=" + sid ;
        }
        if(cid!=null && cid!=-1){
            sql += " and cs.cid=" + cid ;
        }
        if ((dayBeginValue==dayEndValue)&&(dayBeginValue!=null)&&(!"".equals(dayBeginValue.trim()))){
            sql += " and uii.cdate='" + dayBeginValue + "'";
        }else{
            if (dayBeginValue!=null && !"".equals(dayBeginValue.trim())){
                sql += " and uii.cdate>='" + dayBeginValue + "'";
            }
            if (dayEndValue!=null && !"".equals(dayEndValue.trim())){
                sql += " and uii.cdate<='" + dayEndValue + "'";
            }            
        }
        if (null != sname && !"".equals(sname)) {
            sql += " and cs.sname like '%" + sname + "%'";
        }
        sql += " order by uii.cdate desc";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "SELECT uii.*,CONCAT(ic.ichead,ic.icbody) as cardnum,cs.sname as name_student "
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] {  }, new Object[] {  },
                        new UserIcardInfoRowMapper(UserIcardInfoRowMapper.AND_CARDNUM));            
    }
    
    
    @Override
    public Map<String, Object> searchSchoolCardsByCondition(int page,
            int pagesize, Integer sid, Long tid){
        String sql = " FROM `school_icard` si LEFT JOIN ic_card ic on si.icid=ic.icid " +
                "LEFT JOIN school_teacher_icard sti on si.icid=sti.icid where si.sid=? and sti.tid=?";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "SELECT si.*,CONCAT(ic.ichead,ic.icbody) as cardnum,ic.icfoot "
                        +"(select name  from school  s where s.sid=si.sid ) as sname"
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid,tid }, new Object[] { sid,tid },
                        new SchoolIcCardRowMapper(SchoolIcCardRowMapper.AND_CARDNUM_STUDENT));        
    }
    @SuppressWarnings("unchecked")
    @Override
    public List<String> searchSchoolCardNumList(Integer sid) {
        return (List<String>) commonDao
                .list("select CONCAT(ic.ichead,ic.icbody) as cardnum FROM `school_icard` si LEFT JOIN ic_card ic on si.icid=ic.icid where si.sid=?" ,
                        new Object[] {sid},
                        new RowMapper<String>() {
                            @Override
                            public String mapRow(ResultSet rs, int arg1)
                                throws SQLException {
                                return rs.getString("cardnum");
                            }
                        });
       
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> searchSchoolBindCardNumList(Integer sid){
        return (List<String>) commonDao
                .list("select CONCAT(ic.ichead,ic.icbody) as cardnum FROM `school_icard` si LEFT JOIN ic_card ic on si.icid=ic.icid where si.sid=? " +
                        "and ic.icid in (select icid from school_student_icard where sid=?) " +
                        "or ic.icid in (select icid from school_teacher_icard where sid=?)" ,
                        new Object[] {sid,sid,sid},
                        new RowMapper<String>() {
                            @Override
                            public String mapRow(ResultSet rs, int arg1)
                                throws SQLException {
                                return rs.getString("cardnum");
                            }
                        });        
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<String> searchSchoolUnbindCardNumList(Integer sid) {
        return (List<String>) commonDao
                .list("select concat(ic.ichead,ic.icbody) as cardnum from school_icard si left join ic_card ic on si.icid = ic.icid " +
                		"where si.sid = ? and si.state = 10 and si.icid not in (select icid from school_student_icard where sid = ?) and " +
                		"si.icid not in (select icid from school_teacher_icard where sid = ?)" ,
                        new Object[] {sid,sid,sid},
                        new RowMapper<String>() {
                            @Override
                            public String mapRow(ResultSet rs, int arg1)
                                throws SQLException {
                                return rs.getString("cardnum");
                            }
                        });
       
    }
    
    @Override
    public SchoolIcCard searchSchoolIcCard(String icnum, String icfoot,Integer sid){
        String head = icnum.substring(0, 2);
        String body = icnum.substring(2, Constant.num_8);
        return (SchoolIcCard) commonDao.listByObject("select * from school_icard where sid=? and " +
                "icid=(select icid from ic_card where ichead=? and icbody=? and icfoot=?)", 
                new Object[] {sid,head,body,icfoot}, new SchoolIcCardRowMapper());
    }
    
    @Override
    public int updateIcardState(Long icid, short state, Integer sid){
        if (commonDao.update("update school_icard set state=? where sid=? and icid=?",
                new Object[] { state,sid,icid}) > 0) {
            return 1;
        } else {
            return 0;
        }        
    }
    
    @Override
    public int updateIcardState(Long icid, Short state){
        if (commonDao.update("update school_icard set state=? where icid=?",
                new Object[] { state,icid}) > 0) {
            return 1;
        } else {
            return 0;
        }    
    }
    
    @Override
    public int deleteIcard(Long icid){
        commonDao.update("delete from school_student_icard where icid=?",new Object[] { icid});
        commonDao.update("delete from school_teacher_icard where icid=?",new Object[] { icid});
        return 1;
    }
    
    @Override
    public int updateBindCardStudent(Integer sid, Long icid, Long csid) {
        if (commonDao.update("insert into school_student_icard(sid,icid,csid,ctime) values(?,?,?,?)",
                new Object[] { sid,icid,csid,new Timestamp(new Date().getTime())}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateBindCardTeacher(Integer sid, Long icid, Long tid) {
        if (commonDao.update("insert into  school_teacher_icard(sid,icid,tid,ctime) values(?,?,?,?)",
                new Object[] { sid,icid,tid,new Timestamp(new Date().getTime())}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int searchBandCard(Integer sid, Long icid) {
        int num_student = commonDao.countByQuali(" from school_student_icard where sid=? and icid=? ", new Object[] { sid,icid});
        int num_teacher = commonDao.countByQuali(" from school_teacher_icard where sid=? and icid=? ", new Object[] { sid,icid});

        return num_student+num_teacher;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserIcardInfo> searchUserIcardInfoListByCondation(
            Integer sid, Long cid, String dayBeginValue, String dayEndValue,
            String sname) {
        String sql = " from school_student_icard ssi INNER JOIN user_icard_info uii on ssi.icid=uii.icid INNER JOIN ic_card ic on ssi.icid=ic.icid " +
                "INNER JOIN class_student cs on ssi.csid=cs.csid where 1=1 ";
        if(sid!=null && sid!=0){
            sql += " and ssi.sid=" + sid ;
        }
        if(cid!=null && cid!=-1){
            sql += " and cs.cid=" + cid ;
        }
        if ((dayBeginValue==dayEndValue)&&(dayBeginValue!=null)&&(!"".equals(dayBeginValue.trim()))){
            sql += " and uii.cdate='" + dayBeginValue + "'";
        }else{
            if (dayBeginValue!=null && !"".equals(dayBeginValue.trim())){
                sql += " and uii.cdate>='" + dayBeginValue + "'";
            }
            if (dayEndValue!=null && !"".equals(dayEndValue.trim())){
                sql += " and uii.cdate<='" + dayEndValue + "'";
            }            
        }
        if (null != sname && !"".equals(sname)) {
            sql += " and cs.sname like '%" + sname + "%'";
        }
        sql += " order by uii.cdate desc";
        return (List<UserIcardInfo>)commonDao.list("SELECT uii.*,CONCAT(ic.ichead,ic.icbody) as cardnum,cs.sname as name_student "
                + sql,new Object[] {  },new UserIcardInfoRowMapper(UserIcardInfoRowMapper.AND_CARDNUM));
    }

    @Override
    public SchoolIcCard searchSchoolCard(Integer sid, String cardnum) {
        String sql = "select si.* FROM `school_icard` si LEFT JOIN ic_card ic on si.icid=ic.icid where si.sid=? and ic.ichead = ? and ic.icbody = ?";
        return (SchoolIcCard) commonDao.listByObject(sql, 
                new Object[] {sid,cardnum.substring(0, 2),cardnum.substring(2, Constant.num_8)},new SchoolIcCardRowMapper());
    }

    @Override
    public Map<String, Object> searchStuIcardInfoListByCondation(int page,
            int pagesize, Integer sid, Long cid, String dayValue) {
        
        String sql = "select sname as name,(select cdate from user_icard_info where uid = cs.uid and left(cdate,10)='"+dayValue+"' and type = 1 " +
        		"and cdate < '"+dayValue +" 12:00:00' order by cdate desc limit 1) as am_in, (select cdate from user_icard_info " +
        		"where uid = cs.uid and left(cdate,10)='"+dayValue+"' and type = 2 and cdate < '"+dayValue +" 12:00:00' order by cdate " +
        		"limit 1) as am_out, (select cdate from user_icard_info where uid = cs.uid and left(cdate,10)='"+dayValue+"' and type = 1 " +
        		"and cdate > '"+dayValue+" 12:00:00' order by cdate desc limit 1) as pm_in, (select cdate from user_icard_info where uid = " +
        		"cs.uid and left(cdate,10)='"+dayValue+"' and type = 2 and cdate > '"+dayValue +" 12:00:00' order by cdate limit 1) " +
        		"as pm_out from class_student cs ";
        String sql_count = "select count(*) from class_student cs ";
        List<Object> lo = new ArrayList<Object>();
        if (null != cid && -1 != cid) {
            sql += "where cs.cid = ?";
            sql_count += "where cs.cid = ?";
            lo.add(cid);
        } else {
            sql += "left join classes c on cs.cid = c.cid where c.sid = ?";
            sql_count += "left join classes c on cs.cid = c.cid where c.sid = ?";
            lo.add(sid);
        }
        sql += " order by case when am_in is null then 0 else 1 end ,am_in desc";
        return commonDao
                .queryPage(sql, sql_count, page,
                        pagesize, lo, lo,
                        new UserIcardRecordRowMapper());  
    }
    
    @Override
    public Map<String, Object> searchTeaIcardInfoListByDay(int page,
            int pagesize, Integer sid, String dayValue) {
        String sql = "select tname as name,(select cdate from teacher_icard_info where tid = st.tid and left(cdate,10)='"+dayValue+"' and type = 1 " +
        		"and cdate < '"+dayValue+" 12:00:00' order by cdate desc limit 1) as am_in, (select cdate from teacher_icard_info " +
        		"where tid = st.tid and left(cdate,10)='"+dayValue+"' and type = 2 and cdate < '"+dayValue+" 12:00:00' order by cdate " +
        		"limit 1) as am_out, (select cdate from teacher_icard_info where tid = st.tid and left(cdate,10)='"+dayValue+"' and " +
        		"type = 1 and cdate > '"+dayValue+" 12:00:00' order by cdate desc limit 1) as pm_in, (select cdate from teacher_icard_info " +
        		"where tid = st.tid and left(cdate,10)='"+dayValue+"' and type = 2 and cdate > '"+dayValue+" 12:00:00' order by cdate " +
        		"limit 1) as pm_out from school_teacher st where st.sid=? order by case when am_in is null then 0 else 1 end ,am_in desc";
        String sql_count = "select count(*) from school_teacher st where st.sid=?";
        return commonDao
                .queryPage(sql, sql_count, page,
                        pagesize, new Object[]{sid}, new Object[]{sid},
                        new UserIcardRecordRowMapper()); 
    }
    @SuppressWarnings("unchecked")
    @Override
    public List<UserIcardRecord> searchTeaIcardInfoListByDay(Integer sid,
            String dayValue) {
        String sql = "select tname as name,(select cdate from teacher_icard_info where tid = st.tid and left(cdate,10)='"+dayValue+"' and type = 1 " +
                "and cdate < '"+dayValue+" 12:00:00' order by cdate desc limit 1) as am_in, (select cdate from teacher_icard_info " +
                "where tid = st.tid and left(cdate,10)='"+dayValue+"' and type = 2 and cdate < '"+dayValue+" 12:00:00' order by cdate " +
                "limit 1) as am_out, (select cdate from teacher_icard_info where tid = st.tid and left(cdate,10)='"+dayValue+"' and " +
                "type = 1 and cdate > '"+dayValue+" 12:00:00' order by cdate desc limit 1) as pm_in, (select cdate from teacher_icard_info " +
                "where tid = st.tid and left(cdate,10)='"+dayValue+"' and type = 2 and cdate > '"+dayValue+" 12:00:00' order by cdate " +
                "limit 1) as pm_out from school_teacher st where st.sid=? order by case when am_in is null then 0 else 1 end ,am_in desc";
        return (List<UserIcardRecord>)commonDao.list(sql,new Object[] {sid},new UserIcardRecordRowMapper());
    }

    @Override
    public Map<String, Object> searchTeaIcardInfoListByMonth(int page,
            int pagesize, String dayValue, String account,Integer sid) {
        String sql = "select right(left(tii.cdate,10),5) as name, (select cdate from teacher_icard_info where tid = st.tid and type = 1 and " +
        		"left(cdate,10) = left(tii.cdate,10) and right(cdate,8) < '12:00:00' order by cdate desc limit 1) as am_in, " +
        		"(select cdate from teacher_icard_info where tid = st.tid and type = 2 and left(cdate,10) = left(tii.cdate,10) and " +
        		"right(cdate,8) < '12:00:00' order by cdate limit 1) as am_out, (select cdate from teacher_icard_info where tid = st.tid " +
        		"and type = 1 and left(cdate,10) = left(tii.cdate,10) and right(cdate,8) > '12:00:00' order by cdate desc limit 1) " +
        		"as pm_in, (select cdate from teacher_icard_info where tid = st.tid and type = 2 and left(cdate,10) = left(tii.cdate,10) " +
        		"and right(cdate,8) > '12:00:00' order by cdate limit 1) as pm_out from teacher_icard_info tii left join school_teacher st " +
        		"on tii.tid = st.tid left join `user` u on st.uid = u.uid where u.account = ? and st.sid = ? and left(tii.cdate,7) = ? " +
        		"group by left(tii.cdate,10)";
        String sql_count = "select count(*) from teacher_icard_info tii left join school_teacher st on tii.tid = st.tid left join `user` u " +
        		"on st.uid = u.uid where u.account = ? and st.sid = ? and left(tii.cdate,7) = ? group by left(tii.cdate,10)";
        return commonDao
                .queryPage(sql, sql_count, page,
                        pagesize, new Object[]{account,sid,dayValue}, new Object[]{account,sid,dayValue},
                        new UserIcardRecordRowMapper()); 
        
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserIcardRecord> searchTeaIcardInfoListByMonth(String dayValue,
            String account,Integer sid) {
        String sql = "select right(left(tii.cdate,10),5) as name, (select cdate from teacher_icard_info where tid = st.tid and type = 1 and " +
                "left(cdate,10) = left(tii.cdate,10) and right(cdate,8) < '12:00:00' order by cdate desc limit 1) as am_in, " +
                "(select cdate from teacher_icard_info where tid = st.tid and type = 2 and left(cdate,10) = left(tii.cdate,10) and " +
                "right(cdate,8) < '12:00:00' order by cdate limit 1) as am_out, (select cdate from teacher_icard_info where tid = st.tid " +
                "and type = 1 and left(cdate,10) = left(tii.cdate,10) and right(cdate,8) > '12:00:00' order by cdate desc limit 1) " +
                "as pm_in, (select cdate from teacher_icard_info where tid = st.tid and type = 2 and left(cdate,10) = left(tii.cdate,10) " +
                "and right(cdate,8) > '12:00:00' order by cdate limit 1) as pm_out from teacher_icard_info tii left join school_teacher st " +
                "on tii.tid = st.tid left join `user` u on st.uid = u.uid where u.account = ? and st.sid = ? and left(tii.cdate,7) = ? " +
                "group by left(tii.cdate,10)";
        return (List<UserIcardRecord>)commonDao.list(sql,new Object[] {account,sid,dayValue},new UserIcardRecordRowMapper());
    }

    @Override
    public Map<String, Object> searchTeaIcardInfoListByMonthTid(int page,
            int pagesize, String dayValue, Long tid) {
        String sql = "select right(left(tii.cdate,10),5) as name, (select cdate from teacher_icard_info where tid = ? and type = 1 and " +
                "left(cdate,10) = left(tii.cdate,10) and right(cdate,8) < '12:00:00' order by cdate desc limit 1) as am_in, " +
                "(select cdate from teacher_icard_info where tid = ? and type = 2 and left(cdate,10) = left(tii.cdate,10) and " +
                "right(cdate,8) < '12:00:00' order by cdate limit 1) as am_out, (select cdate from teacher_icard_info where tid = ? " +
                "and type = 1 and left(cdate,10) = left(tii.cdate,10) and right(cdate,8) > '12:00:00' order by cdate desc limit 1) " +
                "as pm_in, (select cdate from teacher_icard_info where tid = ? and type = 2 and left(cdate,10) = left(tii.cdate,10) " +
                "and right(cdate,8) > '12:00:00' order by cdate limit 1) as pm_out from teacher_icard_info tii where tii.tid = ? and " +
                "left(tii.cdate,7) = ? group by left(tii.cdate,10)";
        String sql_count = "select count(*) from teacher_icard_info tii where tii.tid = ? and left(tii.cdate,7) = ? group by left(tii.cdate,10)";
        return commonDao
                .queryPage(sql, sql_count, page,
                        pagesize, new Object[]{tid,tid,tid,tid,tid,dayValue}, new Object[]{tid,dayValue},
                        new UserIcardRecordRowMapper()); 
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<UserIcardRecord> searchTeaIcardInfoListByMonthTid(String dayValue, Long tid) {
        String sql = "select right(left(tii.cdate,10),5) as name, (select cdate from teacher_icard_info where tid = ? and type = 1 and " +
                "left(cdate,10) = left(tii.cdate,10) and right(cdate,8) < '12:00:00' order by cdate desc limit 1) as am_in, " +
                "(select cdate from teacher_icard_info where tid = ? and type = 2 and left(cdate,10) = left(tii.cdate,10) and " +
                "right(cdate,8) < '12:00:00' order by cdate limit 1) as am_out, (select cdate from teacher_icard_info where tid = ? " +
                "and type = 1 and left(cdate,10) = left(tii.cdate,10) and right(cdate,8) > '12:00:00' order by cdate desc limit 1) " +
                "as pm_in, (select cdate from teacher_icard_info where tid = ? and type = 2 and left(cdate,10) = left(tii.cdate,10) " +
                "and right(cdate,8) > '12:00:00' order by cdate limit 1) as pm_out from teacher_icard_info tii where tii.tid = ? and " +
                "left(tii.cdate,7) = ? group by left(tii.cdate,10)";
        return (List<UserIcardRecord>) commonDao.list(sql, new Object[]{tid,tid,tid,tid,tid,dayValue},new UserIcardRecordRowMapper()); 
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchCSByParent( Long uid) {
        return (List<ClassStudent>) commonDao.list("select cs.* from class_student cs left join class_parent cp on cs.csid = cp.csid " +
        		"where cp.uid = ? ",new Object[]{uid},new ClassStudentRowMapper());
    }

    @Override
    public Map<String, Object> searchSelfIcardInfoList(int page, int pagesize,
            Long uid, Long tid) {
        if (tid != null) {
            String sql = "select left(tii.cdate,10) as name, (select cdate from teacher_icard_info where tid = ? and type = 1 and " +
                    "left(cdate,10) = left(tii.cdate,10) and right(cdate,8) < '12:00:00' order by cdate desc limit 1) as am_in, " +
                    "(select cdate from teacher_icard_info where tid = ? and type = 2 and left(cdate,10) = left(tii.cdate,10) and " +
                    "right(cdate,8) < '12:00:00' order by cdate limit 1) as am_out, (select cdate from teacher_icard_info where tid = ? " +
                    "and type = 1 and left(cdate,10) = left(tii.cdate,10) and right(cdate,8) > '12:00:00' order by cdate desc limit 1) " +
                    "as pm_in, (select cdate from teacher_icard_info where tid = ? and type = 2 and left(cdate,10) = left(tii.cdate,10) " +
                    "and right(cdate,8) > '12:00:00' order by cdate limit 1) as pm_out from teacher_icard_info tii where tii.tid = ? " +
                    "group by left(tii.cdate,10) order by left(tii.cdate,10) desc";
            String sql_count = "select count(*) from (select tii.* from teacher_icard_info tii where tii.tid = ? group by left(tii.cdate,10)) as qqq";
            return commonDao
                    .queryPage(sql, sql_count, page,
                            pagesize, new Object[]{tid,tid,tid,tid,tid}, new Object[]{tid},
                            new UserIcardRecordRowMapper());
            
        } else {
            String sql = "select left(uii.cdate,10) as name, (select cdate from user_icard_info where uid = ? and type = 1 and " +
            		"left(cdate,10) = left(uii.cdate,10) and right(cdate,8) < '12:00:00' order by cdate desc limit 1) as am_in, " +
            		"(select cdate from user_icard_info where uid = ? and type = 2 and left(cdate,10) = left(uii.cdate,10) and " +
            		"right(cdate,8) < '12:00:00' order by cdate limit 1) as am_out, (select cdate from user_icard_info where uid = ? " +
            		"and type = 1 and left(cdate,10) = left(uii.cdate,10) and right(cdate,8) > '12:00:00' order by cdate desc limit 1) " +
            		"as pm_in, (select cdate from user_icard_info where uid = ? and type = 2 and left(cdate,10) = left(uii.cdate,10) " +
            		"and right(cdate,8) > '12:00:00' order by cdate limit 1) as pm_out from user_icard_info uii where uii.uid = ? " +
            		"group by left(uii.cdate,10) order by left(uii.cdate,10) desc";
            String sql_count = "select count(*) from (select uii.* from user_icard_info uii where uii.uid = ? group by left(uii.cdate,10)) as qqq";
            return commonDao
                    .queryPage(sql, sql_count, page,
                            pagesize, new Object[]{uid,uid,uid,uid,uid}, new Object[]{uid},
                            new UserIcardRecordRowMapper());
        }
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<UserIcardRecord> searchsearchCSRecordList(Long cid, String dayValue) {
        
        String sql = "select sname as name,(select cdate from user_icard_info where uid = cs.uid and left(cdate,10)='"+dayValue+"' and type = 1 " +
                "and cdate < '"+dayValue +" 12:00:00' order by cdate desc limit 1) as am_in, (select cdate from user_icard_info " +
                "where uid = cs.uid and left(cdate,10)='"+dayValue+"' and type = 2 and cdate < '"+dayValue +" 12:00:00' order by cdate " +
                "limit 1) as am_out, (select cdate from user_icard_info where uid = cs.uid and left(cdate,10)='"+dayValue+"' and type = 1 " +
                "and cdate > '"+dayValue+" 12:00:00' order by cdate desc limit 1) as pm_in, (select cdate from user_icard_info where uid = " +
                "cs.uid and left(cdate,10)='"+dayValue+"' and type = 2 and cdate > '"+dayValue +" 12:00:00' order by cdate limit 1) " +
                "as pm_out from class_student cs where cs.cid = ? order by case when am_in is null then 0 else 1 end ,am_in desc";
        
        return (List<UserIcardRecord>) commonDao.list(sql, new Object[]{cid},new UserIcardRecordRowMapper());  
    }
    
    @Override
    public Map<String, Object> searchClassCardsByCondition(int page,
            int pagesize,Long cid,short state) {
        String sql = " from school_icard si left join school_student_icard ssi on si.icid = ssi.icid left join ic_card ic on ssi.icid = ic.icid " +
        		"left join class_student cs on ssi.csid = cs.csid left join classes c on cs.cid = c.cid where c.cid = ?";
        if (state != -1) {
            sql += " and si.state =" + state;
        }
        sql += " order by si.icid,si.state";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "select si.*,concat(ic.ichead,ic.icbody) as cardnum,cs.sname as name_student,c.cname"
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { cid }, new Object[] { cid },
                        new SchoolIcCardRowMapper(SchoolIcCardRowMapper.AND_CNAME));
    }
    
    @Override
    public Map<String, Object> searchSchoolCardsList(int page,
            int pagesize,int sid,String cardnum,short state,String name,short identity) {
        String sql = " from `school_icard` si left join ic_card ic on si.icid=ic.icid left join school_teacher_icard sti on si.icid=sti.icid " +
        		"left join school_student_icard ssi on si.icid=ssi.icid where si.sid=? ";
        if(cardnum!=null&&!"".equals(cardnum)){
            String head = cardnum.substring(0, 2);
            String body = cardnum.substring(2, Constant.num_8);
            sql += "and si.icid=(select icid from ic_card where ichead ='" + head + "' and icbody ='" + body + "'"+ ")";
        }
        if (state == Constant.num_10) {
            sql += " and ((select sname from class_student where csid=ssi.csid) is not null or " +
            		"(select tname from school_teacher where tid=sti.tid) is not null)";
        }
        if (state == Constant.num_30) {
            sql += " and ((select sname from class_student where csid=ssi.csid) is null and " +
                    "(select tname from school_teacher where tid=sti.tid) is null)";
        }
        if (state == Constant.num_20) {
            sql += " and si.state =" + state;
        }
        if(name!=null&&!"".equals(name)){
            sql += " and ((select sname from class_student where csid=ssi.csid) like '%" + name + "%' or " +
            		"(select tname from school_teacher where tid=sti.tid) like '%" + name + "%')";
        }
        if (identity == 1) {
            sql += " and (select sname from class_student where csid=ssi.csid) is not null";
        }
        if (identity == 2) {
            sql += " and (select tname from school_teacher where tid=sti.tid) is not null";
        }
        
        sql += " order by si.icid,si.state";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "select si.*,concat(ic.ichead,ic.icbody) as cardnum,(select tname from school_teacher where tid=sti.tid) as name_teacher," +
                        "(select sname from class_student where csid=ssi.csid) as name_student," +
                        "(select cname from classes c left join class_student cs on c.cid = cs.cid where cs.csid=ssi.csid) as cname "
                        + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid }, new Object[] { sid },
                        new SchoolIcCardRowMapper(SchoolIcCardRowMapper.AND_NEW));
    }

	@SuppressWarnings("unchecked")
	@Override
	public List<String> SearchClassList(Integer TimeType) {
		String sql=null;
		if(TimeType==1){
			 sql="SELECT left(u.cdate,10) as time FROM `user_icard_info` u  GROUP BY left(u.cdate,10)";
		}else if(TimeType==2){
			 sql="SELECT left(u.cdate,10) as time FROM `teacher_icard_info` u  GROUP BY left(u.cdate,10)";
		}else{
			sql="SELECT left(u.cdate,8) as time FROM `teacher_icard_info` u  GROUP BY left(u.cdate,8)";
		}
		
		return (List<String>) commonDao.list(sql);
	}
    
}
